from fastapi import FastAPI, Request, Form, HTTPException
from fastapi.templating import Jinja2Templates
from fastapi.responses import RedirectResponse
from fastapi.staticfiles import StaticFiles
from urllib.parse import urlencode

from psycopg.errors import UniqueViolation, ForeignKeyViolation

from dblock import create_dpool
from jinjapage import view_page

dblock = create_dpool("host=localhost dbname=examdb user=examdb", min_size=4)

app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="./")


@app.get("/")
async def view_list_grades(request: Request):
    with dblock() as db:
        db.execute("""
        SELECT sn AS stu_sn, name as stu_name FROM student ORDER BY name
        """)
        students = list(db)

    with dblock() as db:
        db.execute("""
        SELECT sn AS cou_sn, name as cou_name FROM course ORDER BY name
        """)
        courses = list(db)

    with dblock() as db:
        db.execute("""
        SELECT g.stu_sn, g.cou_sn, 
            s.name as stu_name, 
            c.name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        ORDER BY stu_sn, cou_sn;
        """)

        items = list(db)

    return view_page(
        request, "grade_list.html", students=students, courses=courses, items=items
    )


@app.get("/grade/edit/{stu_sn}/{cou_sn}")
def view_grade_editor(stu_sn: int, cou_sn: int, request: Request):
    with dblock() as db:
        db.execute(
            """
            SELECT grade FROM course_grade
                WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s;
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn),
        )

        record = db.fetchone()

    if record is None:
        errmsg = f"无此学生{stu_sn}的课程{cou_sn}成绩"
        raise HTTPException(status_code=404, detail=errmsg)

    return view_page(
        request, "grade_edit.html", stu_sn=stu_sn, cou_sn=cou_sn, grade=record.grade
    )


@app.get("/grade/delete/{stu_sn}/{cou_sn}")
def grade_deletion_dialog(request: Request, stu_sn: int, cou_sn: int):
    with dblock() as db:
        db.execute(
            """
        SELECT g.stu_sn, g.cou_sn, 
            s.name as stu_name, 
            c.name as cou_name, 
            g.grade 
        FROM course_grade as g
            INNER JOIN student as s ON g.stu_sn = s.sn
            INNER JOIN course as c  ON g.cou_sn = c.sn
        WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s;
        """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn),
        )

        record = db.fetchone()

    if record is None:
        errmsg = f"无此学生{stu_sn}的课程{cou_sn}成绩"
        raise HTTPException(status_code=404, detail=errmsg)

    return view_page(request, "grade_dialog_deletion.html", record=record)


def redirect_error(message: str, return_path: str):
    # query = urlencode({"message": "已经添加该学生的课程成绩", "return": "/"})

    query = urlencode({"message": message, "return_path": return_path})
    return RedirectResponse(url=f"/error?{query}", status_code=302)


@app.get("/error")
async def dialog_error(request: Request, message: str, return_path: str):
    return view_page(
        request, "dialog_error.html", message=message, return_path=return_path
    )


@app.post("/action/grade/add")
async def action_grade_add(
    stu_sn: int = Form(...), cou_sn: int = Form(...), grade: float = Form(...)
):
    try:
        with dblock() as db:
            db.execute(
                """
                INSERT INTO course_grade (stu_sn, cou_sn, grade) 
                VALUES ( %(stu_sn)s, %(cou_sn)s, %(grade)s)
                """,
                dict(stu_sn=stu_sn, cou_sn=cou_sn, grade=grade),
            )
    except UniqueViolation:
        return redirect_error(f"学生{stu_sn}的课程{cou_sn}成绩已添加", return_path="/")
    except ForeignKeyViolation as ex:
        errmsg = f"无此学生或课程: {ex}"
        raise HTTPException(status_code=400, detail=errmsg)

    return RedirectResponse(url="/", status_code=302)


@app.post("/action/grade/edit/{stu_sn}/{cou_sn}")
async def edit_grade_action(stu_sn: int, cou_sn: int, grade: float = Form(...)):
    with dblock() as db:
        db.execute(
            """
            UPDATE course_grade SET grade=%(grade)s
            WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn, grade=grade),
        )

    return RedirectResponse(url="/", status_code=302)


@app.post("/action/grade/delete/{stu_sn}/{cou_sn}")
def delete_grade_action(stu_sn: int, cou_sn: int):
    with dblock() as db:
        db.execute(
            """
            DELETE FROM course_grade
                WHERE stu_sn = %(stu_sn)s AND cou_sn = %(cou_sn)s
            """,
            dict(stu_sn=stu_sn, cou_sn=cou_sn),
        )

    return RedirectResponse(url="/", status_code=302)
